package sg.edu.nus.dashboard.service.stat;

import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Vector;

import sg.edu.nus.dashboard.entity.OpdDBStat;
import sg.edu.nus.dashboard.service.sqlexecutor.SQLService;
import sg.edu.nus.dashboard.vo.ParaStatVo;

public class DatabaseStatService {

	private static final String JAVA_DATE_FORMAT = "yyyyMMdd HHmmss";
	private static final String ORACLE_DATE_FORMAT = "yyyyMMdd hh24miss";

	// get the statistic data by parameter name and time range
	public List<ParaStatVo> getStatByName(String paraName, Date fromTime,
			Date toTime) {

		try {
			List<ParaStatVo> list = new ArrayList<ParaStatVo>();
			String sql = this.constructStatSQL(paraName, fromTime, toTime);
			System.out.println(sql);
			SQLService s = new SQLService();
			List result = s.execute(sql);
			for (int i = 1; i < result.size(); i++) {
				Vector v = (Vector) result.get(i);
				ParaStatVo paraStat = this.convertVectorToVo(v, paraName);
				list.add(paraStat);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	// get all statistic data by time range
	public List<OpdDBStat> getAllStatByTime(Date fromTime, Date toTime) {

		try {
			List<OpdDBStat> list = new ArrayList<OpdDBStat>();

			String sql = this.constructAllStatSQL(fromTime, toTime);
			SQLService s = new SQLService();

			List result = s.execute(sql);
			for (int i = 1; i < result.size(); i++) {
				Vector v = (Vector) result.get(i);
				OpdDBStat stat = this.convertVectorToEntry(v);
				list.add(stat);
			}

			return list;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	private String constructStatSQL(String paraName, Date fromTime, Date toTime) {
		String sqlString = null;
		sqlString = "select DB_ID, STAT_TIME, " + paraName
				+ " from OPD_DB_STAT ";
		if (fromTime != null || toTime != null) {
			sqlString += "where 1=1 ";
			if (fromTime != null)
				sqlString += "and stat_time >= to_timestamp('"
						+ toDateString(fromTime) + "', '" + ORACLE_DATE_FORMAT
						+ "') ";
			if (toTime != null)
				sqlString += "and stat_time <= to_timestamp('"
						+ toDateString(toTime) + "', '" + ORACLE_DATE_FORMAT
						+ "') ";
		}
		return sqlString;
	}

	private String constructAllStatSQL(Date fromTime, Date toTime) {

		String sqlString = null;
		sqlString = "select *" + " from OPD_DB_STAT ";
		if (fromTime != null || toTime != null) {
			sqlString += "where 1=1 ";
			if (fromTime != null)
				sqlString += "and stat_time >= to_timestamp('"
						+ toDateString(fromTime) + "', '" + ORACLE_DATE_FORMAT
						+ "') ";
			if (toTime != null)
				sqlString += "and stat_time <= to_timestamp('"
						+ toDateString(toTime) + "', '" + ORACLE_DATE_FORMAT
						+ "') ";
		}
		sqlString += "order by DB_ID";
		return sqlString;
	}

	private ParaStatVo convertVectorToVo(Vector v, String paraName) {
		int dbId = ((java.math.BigDecimal) v.get(0)).intValue();
		Date statTime = toDateObject((Timestamp) v.get(1));
		float ratio = ((java.math.BigDecimal) v.get(2)).floatValue();
		java.sql.Date sStatTime = new java.sql.Date(statTime.getTime());

		ParaStatVo vo = new ParaStatVo();
		vo.setDbId(dbId);
		vo.setStatTime(sStatTime);
		vo.setParaName(paraName);
		vo.setRatio(ratio);
		return vo;
	}

	private OpdDBStat convertVectorToEntry(Vector v) {
		int dbId = ((java.math.BigDecimal) v.get(0)).intValue();
		Date statTime = toDateObject((Timestamp) v.get(1));
		float overallHealth = ((java.math.BigDecimal) v.get(2)).floatValue();
		float bufferCache = ((java.math.BigDecimal) v.get(3)).floatValue();
		float sharedPool = ((java.math.BigDecimal) v.get(4)).floatValue();
		float redoLogBuffer = ((java.math.BigDecimal) v.get(5)).floatValue();
		float sortingMemoryArea = ((java.math.BigDecimal) v.get(6))
				.floatValue();
		float rollbackSegment = ((java.math.BigDecimal) v.get(7)).floatValue();
		float tableSpace = ((java.math.BigDecimal) v.get(8)).floatValue();
		java.sql.Date sStatTime = new java.sql.Date(statTime.getTime());

		OpdDBStat opdDBStat = new OpdDBStat();
		opdDBStat.setDbId(dbId);
		opdDBStat.setStatTime(sStatTime);
		opdDBStat.setOverallHealth(overallHealth);
		opdDBStat.setBufferCache(bufferCache);
		opdDBStat.setSharedPool(sharedPool);
		opdDBStat.setRedoLogBuffer(redoLogBuffer);
		opdDBStat.setSortingMemoryArea(sortingMemoryArea);
		opdDBStat.setRollbackSegment(rollbackSegment);
		opdDBStat.setTableSpace(tableSpace);

		return opdDBStat;
	}

	private String toDateString(Date inDate) {
		String dateString = null;
		SimpleDateFormat s = new SimpleDateFormat(JAVA_DATE_FORMAT);
		dateString = s.format(inDate);
		return dateString;
	}

	private Date toDateObject(Timestamp timestamp) {
		Date date = new Date(timestamp.getTime());
		return date;
	}

	public static void main(String[] args) {

	}
}
